package com.ruoyi.tongji.run;

import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.*;
import com.ruoyi.tongji.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 宣传员任务数统计
 */
public class 每月联络员统计 {

    public static void main(String[] args) {
        Date dateRangeStart = DateUtil.getDate("2024-09-01 0000000","yyyy-MM-dd HHmmsss");//把这个变量的日期改成统计开始日期
        Date dateRangeEnd = DateUtil.getDate("2024-09-10 2359000","yyyy-MM-dd HHmmsss");//把这个变量的日期改成统计截止日期
        每月员工统计.start(dateRangeStart,dateRangeEnd);
    }

    public static File start(Date dateRangeStart, Date dateRangeEnd) {
        String month = DateUtils.getMonth(dateRangeStart);//以开始时间所在月份作为月份
        String root = "E:\\文档\\2024\\每月统计\\" + month + "月\\程序查询";

        SQLManager jiangzhanSqlManager = ManagerFactory.jiangzhanSqlManager();
        List<HashMap> limkmanList = jiangzhanSqlManager.execute(new SQLReady("select * from 宣传员", new Object[] {}), HashMap.class);

        Map model = new HashMap();
        model.put("limkmanList", limkmanList);
        for (Iterator<HashMap> iterator = limkmanList.iterator(); iterator.hasNext(); ) {

            HashMap linkman =  iterator.next();
            getLinkman(linkman,dateRangeStart, dateRangeEnd);

            if (StringUtils.equals(linkman.get("name").toString(), "曹建涛")) {
                linkman.put("name", "曹桂勇");
            }
            if (StringUtils.equals(linkman.get("name").toString(), "张秀春")) {
                linkman.put("name", "岳良全");
            }
        }


        String excelTemplate = "excelTemplates/联络员/联络员袋数.xlsx";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\联络员" +  month + "月.xlsx");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);

            return f;
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }
    }


    public static Map getLinkman(HashMap linkman, Date dateRangeStart, Date dateRangeEnd) {
        String dateStringRangeStart = DateUtil.format(dateRangeStart, "yyyyMMdd0000000");
        String dateStringRangeEnd = DateUtil.format(dateRangeEnd, "yyyyMMdd2300000");

        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();
        //按照采浆时间算
        List<HashMap> daishuList = taibangSqlManager.execute(new SQLReady("select count(*) as 袋数 from \n" +
                "t_bims_donor_register as register \n" +
                "left join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "left join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "left join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 \n" +
                "and (INSTR(d.remark,'翟毅') = 0 " +
                "and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0 and INSTR(d.remark,'李灿灿') = 0 and INSTR(d.remark,'李敏') = 0 and INSTR(d.remark,'万金瑶') = 0 and INSTR(d.remark,'李明宇') = 0 and INSTR(d.remark,'王笛') = 0 and INSTR(d.remark,'刘文佳') = 0 and INSTR(d.remark,'赵文璐') = 0" +
                ")" +
                "", new Object[] {dateStringRangeStart,dateStringRangeEnd,linkman.get("name") }), HashMap.class);
        Long daishu = (Long)daishuList.get(0).get("袋数");
        linkman.put("daishu", daishu);

        //人数
        //按照采浆时间算
        List<HashMap> renshuList = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from (select count(*) as 人数 from \n" +
                "t_bims_donor_register as register \n" +
                "left join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "left join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "left join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 \n" +
                "and (INSTR(d.remark,'翟毅') = 0 " +
                "and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0 and INSTR(d.remark,'李灿灿') = 0 and INSTR(d.remark,'李敏') = 0 and INSTR(d.remark,'万金瑶') = 0 and INSTR(d.remark,'李明宇') = 0 and INSTR(d.remark,'王笛') = 0 and INSTR(d.remark,'刘文佳') = 0 and INSTR(d.remark,'赵文璐') = 0" +
                ") group by d.donorId) as a" +
                "", new Object[] {dateStringRangeStart,dateStringRangeEnd,linkman.get("name") }), HashMap.class);
        Long renshu = (Long)renshuList.get(0).get("人数");
        linkman.put("renshu", renshu);
        //按照采浆时间算
        List<HashMap> chaobannianList = taibangSqlManager.execute(new SQLReady("select count(*) as  超半年数 from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(regDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(collectionDate,8),'%Y%m%d'))) as days from (\n" +
                "select donorId as aa, registerDate as regDate\n" +
                ",(select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < regDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as collectionDate\n" +
                " from (\n" +
                "select register.* from \n" +
                "t_bims_donor_register as register \n" +
                "left join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "left join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "left join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless = 'N' \n" +
                "and (INSTR(d.remark,'翟毅') = 0 " +
                "and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0 and INSTR(d.remark,'李灿灿') = 0 and INSTR(d.remark,'李敏') = 0 and INSTR(d.remark,'万金瑶') = 0 and INSTR(d.remark,'李明宇') = 0 and INSTR(d.remark,'王笛') = 0 and INSTR(d.remark,'刘文佳') = 0 and INSTR(d.remark,'赵文璐') = 0" +
                ")" +
                "" +
                "group by d.donorId\n" +
                ") as a\n" +
                ") as b\n" +
                ") as c where days > (180)", new Object[] {dateStringRangeStart,dateStringRangeEnd,linkman.get("name") }), HashMap.class);
        System.out.println(chaobannianList);
        Long chaoCount = (Long)chaobannianList.get(0).get("超半年数");
        linkman.put("chaoCount", chaoCount);
        //按照采浆时间算
        List<HashMap> jianyiqiList = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from (\n" +
                "select *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(上次献浆时间,8),'%Y%m%d'))) as 献浆间隔 from (\n" +
                "select d.donorName,d.donorId as dId, register.recordDate as rDate,\n" +
                "(select c.recordDate from t_bims_plasma_collection as c where c.donorId = dId and c.recordDate < rDate order by c.recordDate desc limit 0,1 ) as 上次献浆时间\n" +
                " from \n" +
                "t_bims_donor_register as register \n" +
                "left join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "left join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "left join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0 and register.changeless != 'N' \n" +
                "and (INSTR(d.remark,'翟毅') = 0 " +
                "and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0 and INSTR(d.remark,'李灿灿') = 0 and INSTR(d.remark,'李敏') = 0 and INSTR(d.remark,'万金瑶') = 0 and INSTR(d.remark,'李明宇') = 0 and INSTR(d.remark,'王笛') = 0 and INSTR(d.remark,'刘文佳') = 0 and INSTR(d.remark,'赵文璐') = 0" +
                ")" +
                "group by d.donorId\n" +
                ") as a \n" +
                ") as b where b.献浆间隔 > (90) order by b.献浆间隔 desc", new Object[] {dateStringRangeStart,dateStringRangeEnd,linkman.get("name") }), HashMap.class);
        Long jianXianCount = (Long)jianyiqiList.get(0).get("人数");
        linkman.put("jianXianCount", jianXianCount);

        List<HashMap> xinCountList = taibangSqlManager.execute(new SQLReady("select count(*) as 新浆员数 from (\n" +
                "select register.* from \n" +
                "t_bims_donor_register as register \n" +
                "left join t_bims_linkman as linkman on register.linkmanId = linkman.linkmanId \n" +
                "left join t_bims_plasma_collection as collection on LEFT(register.recordDate,8) = LEFT(collection.recordDate,8) and register.donorId = collection.donorId\n" +
                "left join t_bims_donor as d on register.donorId = d.donorId\n" +
                " where d.firstCollectDate between ? and ? and register.recordDate between ? and ? and linkmanName = ? and collection.weight > 0  \n" +
                "and (INSTR(d.remark,'翟毅') = 0 " +
                "and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0 and INSTR(d.remark,'李灿灿') = 0 and INSTR(d.remark,'李敏') = 0 and INSTR(d.remark,'万金瑶') = 0 and INSTR(d.remark,'李明宇') = 0 and INSTR(d.remark,'王笛') = 0 and INSTR(d.remark,'刘文佳') = 0 and INSTR(d.remark,'赵文璐') = 0" +
                ")" +
                " group by d.donorId\n" +
                ") as a", new Object[] {dateStringRangeStart,dateStringRangeEnd,dateStringRangeStart,dateStringRangeEnd,linkman.get("name") }), HashMap.class);
        Long xinCount = (Long)xinCountList.get(0).get("新浆员数");
        linkman.put("xinCount", xinCount);


        return null;
    }
}
